package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.dudu.model.PoolAbilityInfo;
import com.dudu.model.PoolNumber;

import core.CoreDao;
import core.db.DBConnectionUtil;
/**
 * 号码池管理dao
 * @author liyalei
 * @version 2016年2月29日 上午9:16:03
 *
 */
public class PoolNumberDao {
	private static final Logger log =  core.util.Logger.getLogger(PoolNumberDao.class.getSimpleName());
	
	
	/**
	 * 新建PoolNumber信息
	 * @param dao
	 * @param numberPool 
	 * @return 成功返回0，失败返回-1
	 */
	public int insert(CoreDao dao, PoolNumber numberPool) {
		log.info("PoolNumberDao.insert");
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//执行sql拼写
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_pool_number(phone_number, number_state, number_vest_name, number_vest, beautiful_type, number_source, number_comment, is_resue, create_time, update_time, create_user, update_user)");
			sql.append(" VALUES(?,?,?,?,?,?,?,?,sysdate(),sysdate(),?,?)");
			log.info("insert.sql="+sql);
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						numberPool.getPhone_number(),
						numberPool.getNumber_state(),
						numberPool.getNumber_vest_name(),
						numberPool.getNumber_vest(),
						numberPool.getBeautiful_type(),
						numberPool.getNumber_source(),
						numberPool.getNumber_comment(),
						numberPool.getIs_resue(),
						numberPool.getCreate_user(),
						numberPool.getUpdate_user()
			});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	/**
	 * 编辑PoolNumber信息
	 * @param dao
	 * @param numberPool
	 * @return 成功返回0，失败返回-1
	 */
	public int update(CoreDao dao, PoolNumber numberPool) {
		log.info("PoolNumberDao.update");
		//数据库连接
		Connection con = null;
		int result = -1;

		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写SQL
			StringBuffer sql = new StringBuffer(
					" UPDATE t_pool_number set phone_number=?,number_state=?,number_vest_name=?,number_vest=?,beautiful_type=?,number_source=?,number_comment=?,is_resue=?,update_time=?,update_user=?");
			sql.append(" where id=?");
			log.info("update.sql="+sql);
			//执行sql，更新相应数据
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {
						numberPool.getPhone_number(),
						numberPool.getNumber_state(),
						numberPool.getNumber_vest_name(),
						numberPool.getNumber_vest(),
						numberPool.getBeautiful_type(),
						numberPool.getNumber_source(),
						numberPool.getNumber_comment(),
						numberPool.getIs_resue(),
						numberPool.getUpdate_time(),
						numberPool.getUpdate_user(),
						numberPool.getId()
			});

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	
	/**
	 * 根据ID查询相应信息
	 * @param dao
	 * @param pool_id
	 * @return
	 */
	public PoolNumber queryPoolNumberById(CoreDao dao, String pool_id) {
		log.info("PoolNumberDao.queryPoolNumberById");
		//数据库连接
		Connection con = null;
		//最后需返回的
		PoolNumber result = null;
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT * FROM t_pool_number WHERE id=?");
			
			log.info("queryPoolNumberById="+sql.toString());
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { pool_id });
			//将json类型转化为NumberPool
			if (list != null) {
				List<PoolNumber> items = dao.covertToBean(list, PoolNumber.class);
				if(items.size()>0){
					result=items.get(0);
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	/**
	 * 查询所有PoolNumber信息
	 * @param dao
	 * @param pageSql
	 * @param paramSql
	 * @return
	 */
	public List<PoolNumber> queryAllNumber(CoreDao dao,String pageSql, String paramSql) {
		log.info("PoolNumberDao.queryAllNumber");
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<PoolNumber> result = null;
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tpn.id,tpn.phone_number,tpn.number_state,tpn.number_vest_name,tpn.number_vest,tpn.beautiful_type,tpn.number_source,tpn.number_comment,tpn.is_resue,tpn.create_time,tpn.update_time,tpn.create_user,tpn.update_user");
			sql.append(" FROM t_pool_number tpn");
			
			//判断如果查询条件不为空，则拼写sql
			if(StringUtils.isNotEmpty(paramSql)){
				sql.append(" WHERE ");
				sql.append(paramSql);
			}
			sql.append(" ORDER BY tpn.create_time DESC ");
			sql.append(pageSql);
			log.info("queryAllNumberPool="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
			//将json类型转化为NumberPool
			if (list != null) {
				List<PoolNumber> items = dao.covertToBean(list, PoolNumber.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	/**
	 * 获取号码池信息的条数
	 * @param dao
	 * @param paramSql
	 * @return
	 */
	public int getTotalNumber(CoreDao dao, String paramSql) {
		log.info("PoolNumberDao.getTotalNumber");
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}else{
			paramSql="";
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer("select count(1) as totalNumberPool from t_pool_number tpn where 1=1");
			sql.append(paramSql);

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { });

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalNumberPool");
			log.info("getTotalNumber="+sql.toString());

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	/**
	 * 根据手机号码前7位，查询手机号码归属地
	 * @param dao
	 * @param number
	 * @return
	 */
	public String queryAreaByNum(CoreDao dao,String number) {
		log.info("PoolNumberDao.queryAreaByNum");
		//数据库连接
		Connection con = null;
		//最后需返回的
		String result = null;
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT t.area AS number_vest_name FROM t_mobilephone t WHERE t.mobile_number=?");
			
			log.info("queryAreaByNum="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { number });
			//将json类型转化为NumberPool
			if (list != null) {
				List<PoolNumber> items = dao.covertToBean(list, PoolNumber.class);
				result = items.get(0).getNumber_vest_name();
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	/**
	 * 根据手机号码前7位，查询手机号码归属地编码
	 * @param dao
	 * @param number
	 * @return
	 */
	public String queryAreaCodeByNum(CoreDao dao,String number) {
		log.info("PoolNumberDao.queryAreaCodeByNum");
		//数据库连接
		Connection con = null;
		//最后需返回的
		String result = null;
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT t.area_code AS number_vest FROM t_mobilephone t WHERE t.mobile_number=?");
			
			log.info("queryAreaCodeByNum="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { number });
			//将json类型转化为NumberPool
			if (list != null) {
				List<PoolNumber> items = dao.covertToBean(list, PoolNumber.class);
				result = items.get(0).getNumber_vest();
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	
	/**
	 * 根据能力类型，查询所有已配置能力的号码
	 * @param dao
	 * @param pageSql
	 * @param paramSql
	 * @param accountId 账号id
	 * @param abilityType 能力类型
	 * @return
	 */
	public List<PoolNumber> queryNumbersByAbility(CoreDao dao,String pageSql, String paramSql, String accountId, String abilityType) {
		log.info("PoolNumberDao.queryNumbersByAbility");
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<PoolNumber> result = null;
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tpr.id,tpn.phone_number,tpn.number_vest_name");
			sql.append(" FROM t_pool_account_rel tpr,t_pool_ability_info tpa,t_pool_number tpn");
			sql.append(" WHERE tpr.pool_num_id=tpa.id AND tpn.id=tpa.pool_id");
			sql.append(" AND tpr.account_id=? AND tpa.ability_type=?");
			sql.append(paramSql);
			sql.append(" ORDER BY tpn.phone_number ");
			sql.append(pageSql);
			log.info("queryNumbersByAbility="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { accountId, abilityType });
			//将json类型转化为NumberPool
			if (list != null) {
				List<PoolNumber> items = dao.covertToBean(list, PoolNumber.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	/**
	 * 根据能力类型，查询所有已配置能力的号码的个数
	 * @param dao
	 * @param paramSql
	 * @param accountId 账号id
	 * @param abilityType 能力类型
	 * @return
	 */
	public int getTotalNumberByAbility(CoreDao dao, String paramSql, String accountId, String abilityType) {
		log.info("PoolNumberDao.getTotalNumberByAbility");
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer("SELECT count(1) AS totalNumberPool FROM(");
			sql.append(" SELECT tpr.id,tpn.phone_number,tpn.number_vest_name");
			sql.append(" FROM t_pool_account_rel tpr,t_pool_ability_info tpa,t_pool_number tpn");
			sql.append(" WHERE tpr.pool_num_id=tpa.id AND tpn.id=tpa.pool_id");
			sql.append(" AND tpr.account_id=? AND tpa.ability_type=?");
			sql.append(paramSql);
			sql.append(" ) t;");

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { accountId, abilityType });

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalNumberPool");
			log.info("getTotalNumberByAbility="+sql.toString());

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	
	/**
	 * 根据能力类型，查询所有已配置能力而未配置账号的号码
	 * @param dao
	 * @param pageSql
	 * @param paramSql
	 * @param abilityType 能力类型
	 * @param app_key 
	 * @param cust_account
	 * @return
	 */
	public List<PoolNumber> queryUnConfNumByAbility(CoreDao dao, String pageSql, String paramSql, String abilityType, String app_key,String cust_account) {
		log.info("PoolNumberDao.queryUnConfNumByAbility");
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<PoolNumber> result = null;
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tpa.id,tpn.phone_number,tpn.number_vest_name FROM t_pool_ability_info tpa,t_pool_number tpn");
			sql.append(" WHERE tpa.pool_id=tpn.id AND tpa.ability_type=?  AND tpn.id IN");
			sql.append(" (SELECT tpn.id FROM t_pool_ability_info tpa,t_pool_number tpn");
			sql.append(" WHERE tpa.pool_id=tpn.id AND tpa.ability_type=? AND tpn.is_resue='no'");
			sql.append(paramSql);
			sql.append(" AND tpa.pool_id NOT IN(");
			sql.append(" SELECT DISTINCT tpn.id FROM t_pool_account_rel tpr,t_pool_ability_info tpa,t_pool_number tpn");
			sql.append(" WHERE tpr.pool_num_id=tpa.id AND tpn.id=tpa.pool_id AND tpn.is_resue='no')");
			sql.append(" UNION");
//			sql.append(" SELECT DISTINCT tpn.id FROM t_pool_account_rel tpr,t_pool_ability_info tpa,t_pool_number tpn");
//			sql.append(" WHERE tpr.pool_num_id=tpa.id AND tpn.id=tpa.pool_id AND tpn.is_resue='yes'");
			sql.append(" SELECT DISTINCT tpn.id FROM t_pool_number tpn");
			sql.append(" WHERE tpn.is_resue='yes' AND tpn.id NOT IN(");
			sql.append(" SELECT DISTINCT tpa.pool_id FROM t_pool_account_rel tpr,t_pool_ability_info tpa");
			sql.append(" WHERE tpr.pool_num_id=tpa.id AND tpr.app_key=? and tpr.cust_account=? AND tpa.ability_type=?)");
			sql.append(paramSql);
			sql.append(") ORDER BY tpn.phone_number ");
			sql.append(pageSql);
			log.info("queryUnConfNumByAbility="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { abilityType, abilityType, app_key,cust_account, abilityType });
			//将json类型转化为NumberPool
			if (list != null) {
				List<PoolNumber> items = dao.covertToBean(list, PoolNumber.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	/**
	 * 根据能力类型，查询所有已配置能力而未配置账号的号码
	 * @param dao
	 * @param paramSql
	 * @param abilityType 能力类型
	 * @param app_key 
	 * @param cust_account
	 * @return
	 */
	public int getTotalUnConfNum(CoreDao dao, String paramSql, String abilityType, String app_key,String cust_account) {
		log.info("PoolNumberDao.getTotalUnConfNum");
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " AND " + paramSql;
		}else{
			paramSql="";
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer("SELECT count(1) AS totalNumberPool FROM (");
			sql.append(" SELECT tpa.id,tpn.phone_number,tpn.number_vest_name FROM t_pool_ability_info tpa,t_pool_number tpn");
			sql.append(" WHERE tpa.pool_id=tpn.id AND tpa.ability_type=?  AND tpn.id IN");
			sql.append(" (SELECT tpn.id FROM t_pool_ability_info tpa,t_pool_number tpn");
			sql.append(" WHERE tpa.pool_id=tpn.id AND tpa.ability_type=? AND tpn.is_resue='no'");
			sql.append(paramSql);
			sql.append(" AND tpa.pool_id NOT IN(");
			sql.append(" SELECT DISTINCT tpn.id FROM t_pool_account_rel tpr,t_pool_ability_info tpa,t_pool_number tpn");
			sql.append(" WHERE tpr.pool_num_id=tpa.id AND tpn.id=tpa.pool_id AND tpn.is_resue='no')");
			sql.append(" UNION");
			sql.append(" SELECT DISTINCT tpn.id FROM t_pool_number tpn");
			sql.append(" WHERE tpn.is_resue='yes' AND tpn.id NOT IN(");
			sql.append(" SELECT DISTINCT tpa.pool_id FROM t_pool_account_rel tpr,t_pool_ability_info tpa");
			sql.append(" WHERE tpr.pool_num_id=tpa.id AND tpr.app_key=? and tpr.cust_account=? AND tpa.ability_type=?)");
			sql.append(paramSql);
			sql.append(")) t;");

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { abilityType, abilityType, app_key,cust_account, abilityType });

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalNumberPool");
			log.info("getTotalUnConfNum="+sql.toString());

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	
	
	
}
